using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VQPSTEP
    {

        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "YCC":
                case "TST":
                default:
                    #region=========
                    dwReturn["QPSMANDANT"] = "00";
                    dwReturn["QPSPNRKRS"] = "PV";
                    dwReturn["QPSPLFDNR"] = "000002";
                    dwReturn["QPSSTEP"] = "0060";
                    dwReturn["QPSAENZL"] = "1";
                    dwReturn["QPSAENB"] = "SHF     ";
                    dwReturn["QPSAEND"] = "20131114";
                    dwReturn["QPSNEUB"] = "ZLL     ";
                    dwReturn["QPSNEUD"] = "20131109";
                    dwReturn["QPSPRFGRP"] = " ";
                    dwReturn["QPSARTFB"] = "A";
                    dwReturn["QPSAGANG"] = "               ";
                    dwReturn["QPSGENKZ"] = "E";
                    dwReturn["QPSMERKM"] = "TEA1";
                    dwReturn["QPSPMGRP"] = "        ";
                    dwReturn["QPSDYNTAB"] = "TP";
                    dwReturn["QPSPGROE"] = "PP";
                    dwReturn["QPSFEHLANT"] = "410";
                    dwReturn["QPSMESSANZ"] = "10";
                    dwReturn["QPSFORMEL"] = "01";
                    dwReturn["QPSERFART"] = "A";
                    dwReturn["QPSZEIT"] = "0";
                    dwReturn["QPSMENGE"] = "0";
                    dwReturn["QPSKSTKRS"] = "  ";
                    dwReturn["QPSKST"] = "0000";
                    dwReturn["QPSRAGANG"] = "               ";
                    dwReturn["QPSSPGRD"] = "  ";
                    dwReturn["QPSNSWERT"] = "1500";
                    dwReturn["QPSNSUG"] = "0";
                    dwReturn["QPSNSOG"] = "0";
                    dwReturn["QPSNAUS"] = "    ";
                    dwReturn["QPSASWERT"] = "          ";
                    dwReturn["QPSASUG"] = "          ";
                    dwReturn["QPSASOG"] = "          ";
                    dwReturn["QPSAAUS"] = "    ";
                    dwReturn["QPSNEUG"] = "0";
                    dwReturn["QPSNEOG"] = "0";
                    dwReturn["QPSDRUCKKZ"] = "B";
                    dwReturn["QPSUGKZ"] = "S";
                    dwReturn["QPSOGKZ"] = "O";
                    dwReturn["QPSCWEIGHT"] = "0";
                    dwReturn["QPSFPROCCO"] = "0";
                    dwReturn["QPSWE"] = "   ";
                    dwReturn["QPSPKSTKRS"] = "  ";
                    dwReturn["QPSPKST"] = "0000";
                    dwReturn["QPSFILLER"] = "          00  00000000  00000000                                                              ";
                    #endregion
                    break;
            }
        }

        public DataRow Convert2QPSTEP(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryQPSTEP(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
                    dwReturn["QPSMANDANT"] = " ".PadRight(2);
                    dwReturn["QPSPNRKRS"] = " ".PadRight(2);
                    dwReturn["QPSPLFDNR"] = " ".PadRight(6);
                    dwReturn["QPSSTEP"] = " ".PadRight(4);
                    dwReturn["QPSAENZL"] = " ".PadRight(0);
                    dwReturn["QPSAENB"] = " ".PadRight(8);
                    dwReturn["QPSAEND"] = " ".PadRight(0);
                    dwReturn["QPSNEUB"] = " ".PadRight(8);
                    dwReturn["QPSNEUD"] = " ".PadRight(0);
                    dwReturn["QPSPRFGRP"] = " ".PadRight(1);
                    dwReturn["QPSARTFB"] = " ".PadRight(1);
                    dwReturn["QPSAGANG"] = " ".PadRight(15);
                    dwReturn["QPSGENKZ"] = " ".PadRight(1);
                    dwReturn["QPSMERKM"] = " ".PadRight(4);
                    dwReturn["QPSPMGRP"] = " ".PadRight(8);
                    dwReturn["QPSDYNTAB"] = " ".PadRight(2);
                    dwReturn["QPSPGROE"] = " ".PadRight(2);
                    dwReturn["QPSFEHLANT"] = " ".PadRight(0);
                    dwReturn["QPSMESSANZ"] = " ".PadRight(2);
                    dwReturn["QPSFORMEL"] = " ".PadRight(2);
                    dwReturn["QPSERFART"] = " ".PadRight(1);
                    dwReturn["QPSZEIT"] = " ".PadRight(0);
                    dwReturn["QPSMENGE"] = " ".PadRight(0);
                    dwReturn["QPSKSTKRS"] = " ".PadRight(2);
                    dwReturn["QPSKST"] = " ".PadRight(4);
                    dwReturn["QPSRAGANG"] = " ".PadRight(15);
                    dwReturn["QPSSPGRD"] = " ".PadRight(2);
                    dwReturn["QPSNSWERT"] = " ".PadRight(0);
                    dwReturn["QPSNSUG"] = " ".PadRight(0);
                    dwReturn["QPSNSOG"] = " ".PadRight(0);
                    dwReturn["QPSNAUS"] = " ".PadRight(4);
                    dwReturn["QPSASWERT"] = " ".PadRight(10);
                    dwReturn["QPSASUG"] = " ".PadRight(10);
                    dwReturn["QPSASOG"] = " ".PadRight(10);
                    dwReturn["QPSAAUS"] = " ".PadRight(4);
                    dwReturn["QPSNEUG"] = " ".PadRight(0);
                    dwReturn["QPSNEOG"] = " ".PadRight(0);
                    dwReturn["QPSDRUCKKZ"] = " ".PadRight(1);
                    dwReturn["QPSUGKZ"] = " ".PadRight(1);
                    dwReturn["QPSOGKZ"] = " ".PadRight(1);
                    dwReturn["QPSCWEIGHT"] = " ".PadRight(0);
                    dwReturn["QPSFPROCCO"] = " ".PadRight(0);
                    dwReturn["QPSWE"] = " ".PadRight(3);
                    dwReturn["QPSPKSTKRS"] = " ".PadRight(2);
                    dwReturn["QPSPKST"] = " ".PadRight(4);
                    dwReturn["QPSFILLER"] = " ".PadRight(94);
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }

        public void MapQPSTEP(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }

        public void SaveQPSTEP(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryQPSTEP(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapQPSTEP(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertQPSTEP(dwLocal, ITXDBVERSION);
            }
            UpdateQPSTEP(dwLocal);
        }

        public void InsertQPSTEP(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                default:
                case "JLT":
                    or1.CommandText = "INSERT INTO QPSTEP ( QPSMANDANT,QPSPNRKRS,QPSPLFDNR,QPSSTEP,QPSAENZL,QPSAENB,QPSAEND,QPSNEUB,QPSNEUD,QPSPRFGRP,QPSARTFB,QPSAGANG,QPSGENKZ,QPSMERKM,QPSPMGRP,QPSDYNTAB,QPSPGROE,QPSFEHLANT,QPSMESSANZ,QPSFORMEL,QPSERFART,QPSZEIT,QPSMENGE,QPSKSTKRS,QPSKST,QPSRAGANG,QPSSPGRD,QPSNSWERT,QPSNSUG,QPSNSOG,QPSNAUS,QPSASWERT,QPSASUG,QPSASOG,QPSAAUS,QPSNEUG,QPSNEOG,QPSDRUCKKZ,QPSUGKZ,QPSOGKZ,QPSCWEIGHT,QPSFPROCCO,QPSWE,QPSPKSTKRS,QPSPKST,QPSFILLER)"
                    + " VALUES (:QPSMANDANT:,:QPSPNRKRS:,:QPSPLFDNR:,:QPSSTEP:,:QPSAENZL:,:QPSAENB:,:QPSAEND:,:QPSNEUB:,:QPSNEUD:,:QPSPRFGRP:,:QPSARTFB:,:QPSAGANG:,:QPSGENKZ:,:QPSMERKM:,:QPSPMGRP:,:QPSDYNTAB:,:QPSPGROE:,:QPSFEHLANT:,:QPSMESSANZ:,:QPSFORMEL:,:QPSERFART:,:QPSZEIT:,:QPSMENGE:,:QPSKSTKRS:,:QPSKST:,:QPSRAGANG:,:QPSSPGRD:,:QPSNSWERT:,:QPSNSUG:,:QPSNSOG:,:QPSNAUS:,:QPSASWERT:,:QPSASUG:,:QPSASOG:,:QPSAAUS:,:QPSNEUG:,:QPSNEOG:,:QPSDRUCKKZ:,:QPSUGKZ:,:QPSOGKZ:,:QPSCWEIGHT:,:QPSFPROCCO:,:QPSWE:,:QPSPKSTKRS:,:QPSPKST:,:QPSFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case "YGR":
                    or1.CommandText = "INSERT INTO QPSTEP ( QPSMANDANT,QPSPNRKRS,QPSPLFDNR,QPSSTEP,QPSAENZL,QPSAENB,QPSAEND,QPSNEUB,QPSNEUD,QPSPRFGRP,QPSARTFB,QPSAGANG,QPSGENKZ,QPSMERKM,QPSPMGRP,QPSDYNTAB,QPSPGROE,QPSFEHLANT,QPSMESSANZ,QPSFORMEL,QPSERFART,QPSZEIT,QPSMENGE,QPSKSTKRS,QPSKST,QPSRAGANG,QPSSPGRD,QPSNSWERT,QPSNSUG,QPSNSOG,QPSNAUS,QPSASWERT,QPSASUG,QPSASOG,QPSAAUS,QPSNEUG,QPSNEOG,QPSDRUCKKZ,QPSUGKZ,QPSOGKZ,QPSCWEIGHT,QPSFPROCCO,QPSWE,QPSPKSTKRS,QPSPKST,QPSFILLER)"
                    + " VALUES (:QPSMANDANT:,:QPSPNRKRS:,:QPSPLFDNR:,:QPSSTEP:,:QPSAENZL:,:QPSAENB:,:QPSAEND:,:QPSNEUB:,:QPSNEUD:,:QPSPRFGRP:,:QPSARTFB:,:QPSAGANG:,:QPSGENKZ:,:QPSMERKM:,:QPSPMGRP:,:QPSDYNTAB:,:QPSPGROE:,:QPSFEHLANT:,:QPSMESSANZ:,:QPSFORMEL:,:QPSERFART:,:QPSZEIT:,:QPSMENGE:,:QPSKSTKRS:,:QPSKST:,:QPSRAGANG:,:QPSSPGRD:,:QPSNSWERT:,:QPSNSUG:,:QPSNSOG:,:QPSNAUS:,:QPSASWERT:,:QPSASUG:,:QPSASOG:,:QPSAAUS:,:QPSNEUG:,:QPSNEOG:,:QPSDRUCKKZ:,:QPSUGKZ:,:QPSOGKZ:,:QPSCWEIGHT:,:QPSFPROCCO:,:QPSWE:,:QPSPKSTKRS:,:QPSPKST:,:QPSFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;

            }
            Connect2Proxy(or1);
        }

        public DataSet QueryQPSTEP(DataRow dwData, int option = 1)
        {
            DataSet SReturn = new DataSet();
            DataSet SResult = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT M.QPMKURZB,M.QPMMEEH,M.QPMBEZ,S.*  FROM QPSTEP S LEFT JOIN QPMERKM M"
                        + " ON S.QPSMERKM=M.QPMMERKM   WHERE  QPSPNRKRS=:QPSPNRKRS: AND QPSPLFDNR=:QPSPLFDNR:"
                         + " ORDER BY QPSSTEP";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "SELECT M.QPMKURZB,M.QPMMEEH,M.QPMBEZ,QPMNORM,WAPBEM, WAPBIS,WAPME,WAPMIN,WAPMAX,S.*"
                        + "  FROM QPSTEP S LEFT JOIN QPMERKM M ON S.QPSMERKM=M.QPMMERKM"
                        + " LEFT JOIN WAPROF ON RTRIM(QPSMERKM)= RTRIM(WAPANFART)  AND WAPART=:QPZART: AND WAPVAR=:WAPVAR"
                        + " WHERE QPSPNRKRS=:QPSPNRKRS: AND  QPSPLFDNR=:QPSPLFDNR:"
                        + " ORDER BY QPSSTEP";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    or1.Parameters.Add("WAPVAR", OracleType.NVarChar).Value = Helper.Left(dwData["QPZVAR"].ToString(), 2);
                    break;
                default:
                    break;
            }
            SResult = Connect2Proxy(or1);
            if (SResult.Tables.Count > 0)
            {
                SResult.Tables[0].TableName = "QPSTEP";
            }
            SReturn.Merge(SResult);

            return SReturn;
        }

        public void DeleteQPSTEP(DataRow dwData, int option = 1)
        { 
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM QPSTEP "
                   + " WHERE QPSMANDANT=:QPSMANDANT: AND QPSPNRKRS=:QPSPNRKRS: AND QPSPLFDNR=:QPSPLFDNR: AND QPSSTEP=:QPSSTEP: "; 
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);  
                    break;
                case 2:
                    or1.CommandText = "DELETE FROM QPSTEP "
                   + " WHERE QPSMANDANT=:QPSMANDANT: AND QPSPNRKRS=:QPSPNRKRS: AND QPSPLFDNR=:QPSPLFDNR:  ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }

        public void UpdateQPSTEP(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        { 
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0://JLT
                    or1.CommandText = "UPDATE QPSTEP SET QPSMANDANT=:QPSMANDANT:,QPSPNRKRS=:QPSPNRKRS:,QPSPLFDNR=:QPSPLFDNR:,QPSSTEP=:QPSSTEP:,QPSAENZL=:QPSAENZL:,QPSAENB=:QPSAENB:,QPSAEND=:QPSAEND:,QPSNEUB=:QPSNEUB:,QPSNEUD=:QPSNEUD:,QPSPRFGRP=:QPSPRFGRP:,QPSARTFB=:QPSARTFB:,QPSAGANG=:QPSAGANG:,QPSGENKZ=:QPSGENKZ:,QPSMERKM=:QPSMERKM:,QPSPMGRP=:QPSPMGRP:,QPSDYNTAB=:QPSDYNTAB:,QPSPGROE=:QPSPGROE:,QPSFEHLANT=:QPSFEHLANT:,QPSMESSANZ=:QPSMESSANZ:,QPSFORMEL=:QPSFORMEL:,QPSERFART=:QPSERFART:,QPSZEIT=:QPSZEIT:,QPSMENGE=:QPSMENGE:,QPSKSTKRS=:QPSKSTKRS:,QPSKST=:QPSKST:,QPSRAGANG=:QPSRAGANG:,QPSSPGRD=:QPSSPGRD:,QPSNSWERT=:QPSNSWERT:,QPSNSUG=:QPSNSUG:,QPSNSOG=:QPSNSOG:,QPSNAUS=:QPSNAUS:,QPSASWERT=:QPSASWERT:,QPSASUG=:QPSASUG:,QPSASOG=:QPSASOG:,QPSAAUS=:QPSAAUS:,QPSNEUG=:QPSNEUG:,QPSNEOG=:QPSNEOG:,QPSDRUCKKZ=:QPSDRUCKKZ:,QPSUGKZ=:QPSUGKZ:,QPSOGKZ=:QPSOGKZ:,QPSCWEIGHT=:QPSCWEIGHT:,QPSFPROCCO=:QPSFPROCCO:,QPSWE=:QPSWE:,QPSPKSTKRS=:QPSPKSTKRS:,QPSPKST=:QPSPKST:,QPSFILLER=:QPSFILLER:"
                        + " WHERE QPSMANDANT=:QPSMANDANT: AND QPSPNRKRS=:QPSPNRKRS: AND QPSPLFDNR=:QPSPLFDNR: AND QPSSTEP=:QPSSTEP: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break;
                default:
                    break;
            } 
            Connect2Proxy(or1);
        }
    }
}
